const query = require('../../dbConfig/index')
const XLSX = require('xlsx'); 
const get = async (ctx,next)=>{
	const body = ctx.request.body
	const offset = (body.pageNumber - 1) * Number(body.pageSize);  
	let sql = 'SELECT * FROM parent';  
	let params = [];  
	let pageParams=[]
	// 如果传了条件 就拼接sql
	if (body.studentName) {  
		sql += sql.includes('WHERE')? ' and student_id=?': ' WHERE student_id=?';  
		pageParams.push(`student_id='${body.studentName}'`)
		params.push(body.studentName);  
	}
	if (body.parentName) {  
		sql += sql.includes('WHERE')?  'and parent_name=?':' WHERE parent_name=?';  
		pageParams.push(`parent_name='${body.parentName}'`)
		params.push(body.parentName);  
	}
	if (body.classesId) {  
		sql +=sql.includes('WHERE')? ' and classes_id=?': ' WHERE classes_id=?';  
		pageParams.push(`classes_id='${body.classesId}'`)
		params.push(body.classesId);  
	}else if(ctx.state.power!=1&&ctx.state.classes){
		let str = ctx.state.classes.split(",").map(num => parseInt(num))
		sql += sql.includes('WHERE')? ' and classes_id IN (?)':' WHERE classes_id IN (?)';  
		pageParams.push(`classes_id IN (${str})`)
		params.push(str); 
	}
	  sql += ' LIMIT ? OFFSET ?';  
	  params.push(Number(body.pageSize));  
	  params.push(offset);
	const res = await query(sql,params)
	const total = await query(`SELECT COUNT(*) as total FROM parent ${pageParams.length ?('where ' + pageParams.join(' and ')):''}`)
	if(res.length>0){
		ctx.body={code:200,data:{records:res,total:total[0].total},msg:'成功'}
		next()
	}else{
		ctx.body={code:0,data:null,msg:'暂无家长数据'}
	}
}
const getItem = async (ctx,next)=>{
	const body = ctx.request.body 
	let sql = `SELECT * FROM parent where student_id=${body.student_id}`  
	const res = await query(sql)
	if(res.length>0){
		ctx.body={code:200,data:res,msg:'成功'}
		next()
	}else{
		ctx.body={code:0,data:null,msg:'没有家长数据'}
	}
}
const updata = async (ctx,next)=>{
	const body = ctx.request.body
	await query(`UPDATE parent SET student_id = ?,classes_id=?,parent_name=?,parent_sex=?,parent_relation=?,parent_tel=?,parent_password = ? WHERE id='${body.id}'`,[body.student_id,body.classes_id,body.parent_name,body.parent_sex,body.parent_relation,body.parent_tel,body.parent_password])
	ctx.body={code:200,data:null,msg:'更新成功'}
	next()
	
}
const add = async (ctx,next)=>{
	const body = ctx.request.body
	await query(`INSERT INTO parent (student_id,classes_id,parent_name,parent_sex,parent_relation,parent_tel,parent_password) VALUES (?,?,?,?,?,?,?)`,[body.student_id,body.classes_id,body.parent_name,body.parent_sex,body.parent_relation,body.parent_tel,body.parent_password])
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const remove = async (ctx,next)=>{
	const body = ctx.request.body
	// TODO  检查该家长下学生的状态是否退学或毕业 否则不允许删除
	await query(`DELETE FROM parent WHERE id=${body.id}`)
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const exportFile = async (ctx,next)=>{
	const res = await query(`select * FROM parent`)
	const classes = await query(`select * FROM classes`)
	const student = await query(`select * FROM student`)
	const classObj={}
	const studentObj={}
	for(let i=0;i<classes.length;i++){
		classObj[classes[i].classes_id]=classes[i].classes_name
	}
	for(let i=0;i<student.length;i++){
		studentObj[student[i].student_id]=student[i].student_name
	}
	res.forEach(element => {
		element.student_id = studentObj[element.student_id]
		element.parent_sex = Number(element.student_sex)?'女':'男'
		element.classes_id = classObj[element.classes_id]
	});
	const wb = XLSX.utils.book_new();  
	  const ws = XLSX.utils.json_to_sheet(res);  
	  const headers = ['id', '学生姓名', '班级','家长姓名','家长性别','学生关系','家长电话','密码'];
	  const col = ['A1','B1','C1','D1','E1','F1','G1','H1']
	  for(let i=0;i<col.length;i++){
		ws[col[i]] = { t: 's', v: headers[i] };
	  }
	  XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
	  const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' }); 
	  ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');  
  	  ctx.set('Content-Disposition', 'attachment; filename="data.xlsx"');
	  ctx.body = wbout;
	next()
}
const importFile = async (ctx,next)=>{
	const classes = await query(`select * FROM classes`)
	const student = await query(`select * FROM student`)
	const filePath = ctx.request.files.parentsExcel.filepath; 
	const workbook = XLSX.readFile(filePath);
	const worksheet = workbook.Sheets[workbook.SheetNames[0]]; 
	const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 2 }); 
	jsonData.shift()
	try {
		for (let i = 0; i < jsonData.length; i++) {
			delete jsonData[i]['家长信息']
			jsonData[i]['_1']=classes.filter(item=>item.classes_name===jsonData[i]['_1'])[0].classes_id
			jsonData[i][""]=student.filter(item=>item.student_name===jsonData[i][""])[0].student_id
			jsonData[i]['_3']=jsonData[i]['_3']=='女'?'1':'0'
		}

	} catch (error) {
		ctx.body={code:0,data:null,msg:'请核对学生姓名和班级'}
	}

	for(let i=0;i<jsonData.length;i++){
			await query(`INSERT INTO parent (student_id,classes_id,parent_name,parent_sex,parent_relation,parent_tel,parent_password) VALUES ('${jsonData[i][""]}','${jsonData[i]['_1']}','${jsonData[i]['_2']}','${jsonData[i]['_3']}','${jsonData[i]['_4']}','${jsonData[i]['_5']}','${jsonData[i]['_6']}')`)
		}
	
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
module.exports = {
	get,getItem,
	add,updata,remove,
	exportFile,
	importFile
}